using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OneDayModel.DBRepository;
using OneDayModel.DBRepository.MSSQL;
using OneDayModel.Interfaces;

namespace OneDayModel.Tests.Database
{
    [TestClass]
    public class DB_TestGetRoles
    {
        private static SqlConnectionManager connectionManager = new SqlConnectionManager(GetConnectionString());

        [ClassCleanup]
        public static void CloseConnectionForPooling()
        {
            connectionManager.Dispose();
        }
       
        [TestMethod]
        public void BasicGetRolesTest()
        {
            SqlConnection connection = GetConnection();
            IList<IRole> roles;
            try
            {
                IDbFetcher fetcher = new SqlServerFetcher();
                IRepository repository = new DbRepository(connectionManager, fetcher);
                roles = repository.GetRoles();
            }
            finally
            {
                ReleaseConnection(connection);
            }
            Assert.AreEqual(4, roles.Count);
            AssertRole(roles[0], "test_Base", false);
            AssertRole(roles[1], "test_Person", false);
            AssertRole(roles[2], "test_Model", true);
            AssertRole(roles[3], "test_Photographer", true);
        }

        private void AssertRole(IRole role, string roleName, bool isPublic)
        {
            Assert.AreEqual(roleName, role.Name);
            Assert.AreEqual(isPublic, role.Public);
        }

        [TestInitialize]
        public void PrepareRolesDB()
        {
            SqlConnection connection = GetConnection();
            try
            {
                InsertRole(10000, null, "test_Base", false, connection);
                InsertRole(10001, 10000, "test_Person", false, connection);
                InsertRole(10002, 10001, "test_Model", true, connection);
                InsertRole(10003, 10001, "test_Photographer", true, connection);
            }
            finally
            {
                ReleaseConnection(connection);
            }
        }

        private void InsertRole(int id, int? parentRoleId, string roleName, bool isPublic, SqlConnection connection)
        {
            const string rolesDbInsertBase =
                "SET IDENTITY_INSERT roles ON;INSERT INTO roles (id, roles_id, name, is_public) VALUES (@id, @roles_id, @name, @is_public);";
            SqlCommand command = new SqlCommand(rolesDbInsertBase, connection);
            command.Parameters.AddWithValue("@id", id);
            command.Parameters.AddWithValue("@roles_id", parentRoleId.HasValue ? (object) parentRoleId : DBNull.Value);
            command.Parameters.AddWithValue("@name", roleName);
            command.Parameters.AddWithValue("@is_public", isPublic);
            Assert.AreEqual(1, command.ExecuteNonQuery());
        }

        private void ReleaseConnection(SqlConnection connection)
        {
            connectionManager.ReleaseConnection(connection);
        }

        private SqlConnection GetConnection()
        {
            SqlConnection connection = (SqlConnection) connectionManager.AcquireConnection();
            Assert.IsNotNull(connection);
            Assert.IsTrue(connection.State == ConnectionState.Open);
            return connection;
        }

        private static string GetConnectionString()
        {
            ConnectionStringSettings settings =
                ConfigurationManager.ConnectionStrings["onedaymodel"];

            if (null != settings)
            {
                return settings.ConnectionString;
                
            }
            return null;
        }

        [TestCleanup]
        public void CleanRolesDB()
        {
            SqlConnection connection = GetConnection();
            try
            {
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("DELETE FROM roles WHERE name LIKE 'test_%'", connection);
                        command.Transaction = transaction;
                        Assert.AreEqual(4, command.ExecuteNonQuery());
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                    transaction.Commit();
                }

            }
            finally
            {
                ReleaseConnection(connection);
            }
        }
    }
}